package smetana.modules.io;

import com.google.inject.Inject;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.atomic.AtomicInteger;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import smetana.domain.join.DocPositionReport;
import smetana.domain.join.DocReport;
import smetana.modules.business.BusinessDocPosition;
import smetana.util.Pivot;
import smetana.modules.business.BusinessDoc;
import smetana.modules.business.BusinessPivot;

/**
 *
 * @author dmitry.mamonov
 */
public class ExportServiceImpl implements ExportService {
    @Inject
    BusinessDoc businessDoc;
    @Inject
    BusinessDocPosition businessDocPosition;

    @Inject
    BusinessPivot businessPivot;


    @Override
    public File exportDocument(final int documentId) throws IOException {
        return new Callable<File>() {
            DocReport doc;

            List<DocPositionReport> docItemList;

            HSSFWorkbook workbook;

            HSSFSheet sheet;

            DataFormat format;

            Font bold;
            Font boldTitle;
            Font normal;
            Font normalTitle;

            AtomicInteger currentRow = new AtomicInteger(0);

            BigDecimal totalSummaryPricce = BigDecimal.ZERO;

            @Override
            public File call() throws IOException {
                loadData();
                initWorkbookAndSheet();
                arrangeColumns();
                exportReportHeader();
                exportReportTable();
                exportReportTotals();
                exportReportFooter();
                return renderFile();

            }

            private void loadData() {
                doc = businessDoc.findDocReportById(documentId);
                docItemList = businessDocPosition.listDocPositionReports(doc);
            }

            private void initWorkbookAndSheet() throws IOException {
                workbook = new HSSFWorkbook(getClass().getResourceAsStream("workbook_template.xls"));
                sheet = workbook.getSheetAt(0);
                format = workbook.createDataFormat();
                bold = workbook.createFont();
                bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
                bold.setFontHeight((short)(9*20));

                boldTitle = workbook.createFont();
                boldTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
                boldTitle.setFontHeight((short)(10*20));

                normal = workbook.createFont();
                normal.setFontName("Times New Roman");
                normal.setFontHeight((short)(9*20));

                normalTitle = workbook.createFont();
                normalTitle.setFontName("Times New Roman");
                normalTitle.setFontHeight((short)(10*20));
            }

            private void exportReportHeader() {
                final short shift = 1;
                { //Подрядчик:
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    {
                        HSSFCell cell = row.createCell(shift + 0);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                        cell.setCellStyle(style);
                        cell.setCellValue("Подрядчик:");
                    }
                    {
                        HSSFCell cell = row.createCell(shift + 1);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                        cell.setCellStyle(style);
                        row.createCell(shift + 1).setCellValue(doc.getManagerName());
                    }
                }
                { //Заказчик:
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    {
                        HSSFCell cell = row.createCell(shift + 0);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                        cell.setCellStyle(style);
                        cell.setCellValue("Заказчик:");
                    }
                    {
                        HSSFCell cell = row.createCell(shift + 1);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                        cell.setCellStyle(style);
                        row.createCell(shift + 1).setCellValue(doc.getAgentName());
                    }
                }
                { //Адрес:
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    {
                        HSSFCell cell = row.createCell(shift + 0);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                        cell.setCellStyle(style);
                        cell.setCellValue("Адрес:");
                    }
                    {
                        HSSFCell cell = row.createCell(shift + 1);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                        cell.setCellStyle(style);
                        row.createCell(shift + 1).setCellValue(doc.getAddress());
                    }
                }
                { //Договор
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    {
                        HSSFCell cell = row.createCell(shift + 0);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                        cell.setCellStyle(style);
                        cell.setCellValue("Договор:");
                    }
                    {
                        HSSFCell cell = row.createCell(shift + 1);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                        cell.setCellStyle(style);
                        row.createCell(shift + 1).setCellValue(doc.getUserCode());
                    }
                }
                currentRow.incrementAndGet();
                { //Заголовок+Дата.
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    {
                        HSSFCell cell = row.createCell(0);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                        style.setFont(boldTitle);
                        cell.setCellStyle(style);
                        cell.setCellValue("Акт выполненых работ №1 от " + Pivot.formatDateRu(doc.getDateActual()));
                    }
                    mergeRow(row);
                }
                { //Примечание
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    {
                        HSSFCell cell = row.createCell(0);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                        style.setFont(normalTitle);
                        cell.setCellStyle(style);
                        cell.setCellValue(doc.getDescription());
                    }
                    mergeRow(row);
                }
                currentRow.incrementAndGet();
            }

            private void exportReportTable() {
                AtomicInteger currentColumn = new AtomicInteger(0);
                HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                { //1. Номер.
                    HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                    HSSFCellStyle style = getDefaultStyle(cell);
                    applyBorders(style);
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cell.setCellStyle(style);
                    cell.setCellValue("№");
                }
                { //2. Код.
                    HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                    HSSFCellStyle style = getDefaultStyle(cell);
                    applyBorders(style);
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cell.setCellStyle(style);
                    cell.setCellValue("Код");
                }
                { //3. Наименование.
                    HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                    HSSFCellStyle style = getDefaultStyle(cell);
                    applyBorders(style);
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cell.setCellStyle(style);
                    cell.setCellValue("Наименование");
                }
                { //4. Колиечство.
                    HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                    HSSFCellStyle style = getDefaultStyle(cell);
                    applyBorders(style);
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cell.setCellStyle(style);
                    cell.setCellValue("К-во");
                }
                { //5. Единицы.
                    HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                    HSSFCellStyle style = getDefaultStyle(cell);
                    applyBorders(style);
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cell.setCellStyle(style);
                    cell.setCellValue("Ед.");
                }
                { //6. Цена за единицу.
                    HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                    HSSFCellStyle style = getDefaultStyle(cell);
                    applyBorders(style);
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cell.setCellStyle(style);
                    cell.setCellValue("Цена");
                }
                { //7. Цена, общая.
                    HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                    HSSFCellStyle style = getDefaultStyle(cell);
                    applyBorders(style);
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    cell.setCellStyle(style);
                    cell.setCellValue("Сумма");
                }
            }

            private void exportReportTotals() {
                int rowIndex = 1;
                String sectionKey = "";
                for (DocPositionReport docItem : docItemList) {
                    String currentSectionKey = "" + docItem.getPositionTypeName() + " / " + docItem.getPositionGroupName();
                    if (currentSectionKey.equals(sectionKey) == false) {
                        sectionKey=currentSectionKey;
                        { //Заголовок секции.
                            HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                            {
                                HSSFCell cell = row.createCell(0);
                                HSSFCellStyle style = getDefaultStyle(cell);
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                style.setFont(bold);
                                cell.setCellStyle(style);
                                cell.setCellValue(currentSectionKey);
                            }
                            mergeRow(row);
                        }
                    }

                    AtomicInteger currentColumn = new AtomicInteger(0);
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    { //1. Номер.
                        HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                        HSSFCellStyle style = getDefaultStyle(cell);
                        applyBorders(style);
                        cell.setCellStyle(style);
                        cell.setCellValue(rowIndex++);
                    }
                    { //2. Код.
                        HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                        HSSFCellStyle style = getDefaultStyle(cell);
                        applyBorders(style);
                        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                        cell.setCellStyle(style);
                        cell.setCellValue("" + docItem.getPositionId());
                    }
                    { //3. Наименование.
                        HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                        HSSFCellStyle style = getDefaultStyle(cell);
                        applyBorders(style);
                        cell.setCellStyle(style);
                        cell.setCellValue(docItem.getPositionName());
                    }
                    { //4. Колиечство.
                        HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                        HSSFCellStyle style = getDefaultStyle(cell);
                        applyBorders(style);
                        if (docItem.getAmount().subtract(new BigDecimal(docItem.getAmount().intValue())).equals(BigDecimal.ZERO) == false) {
                            style.setDataFormat(format.getFormat("0"));
                        } else {
                            style.setDataFormat(format.getFormat("0.00"));
                        }
                        cell.setCellStyle(style);
                        cell.setCellValue(docItem.getAmount().doubleValue());
                    }
                    { //5. Единицы.
                        HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                        HSSFCellStyle style = getDefaultStyle(cell);
                        applyBorders(style);
                        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                        cell.setCellStyle(style);
                        cell.setCellValue(docItem.getUnit());
                    }
                    { //6. Цена за единицу.
                        HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                        HSSFCellStyle style = getDefaultStyle(cell);
                        applyBorders(style);
                        style.setDataFormat(format.getFormat("#,##0.00р"));
                        cell.setCellStyle(style);
                        cell.setCellValue(docItem.getRealPrice().doubleValue());
                    }
                    { //7. Цена, общая.
                        HSSFCell cell = row.createCell(currentColumn.getAndIncrement());
                        HSSFCellStyle style = getDefaultStyle(cell);
                        applyBorders(style);
                        style.setDataFormat(format.getFormat("#,##0.00р"));
                        cell.setCellStyle(style);
                        cell.setCellValue(docItem.getTotalPrice().doubleValue());
                    }
                    totalSummaryPricce = totalSummaryPricce.add(docItem.getTotalPrice());
                }
            }

            private void exportReportFooter() {
                { //итоги
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    { //6. Цена за единицу.
                        HSSFCell cell = row.createCell(5);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setFont(bold);
                        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                        cell.setCellStyle(style);
                        cell.setCellValue("Всего к оплате: ");
                    }
                    { //7. Цена, общая.
                        HSSFCell cell = row.createCell(6);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        applyBorders(style);
                        style.setFont(bold);
                        style.setDataFormat(format.getFormat("#,##0.00р"));
                        cell.setCellStyle(style);
                        cell.setCellValue(totalSummaryPricce.doubleValue());
                    }
                }

                { //Место печати.
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    {
                        HSSFCell cell = row.createCell(0);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                        cell.setCellStyle(style);
                        cell.setCellValue("М.П.");
                    }
                    mergeRow(row);

                }
                { //подпись.
                    HSSFRow row = sheet.createRow(currentRow.getAndIncrement());
                    { // Подпись пордядчика и заказчика.
                        HSSFCell cell = row.createCell(0);
                        HSSFCellStyle style = getDefaultStyle(cell);
                        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                        cell.setCellStyle(style);
                        cell.setCellValue("Подрядчик _______________          Заказчик  _______________");
                    }
                    mergeRow(row);
                }


                //template: HSSFFooter footer = sheet.getFooter();
                //template: footer.setRight("Страница " + HSSFFooter.page() + " из " + HSSFFooter.numPages());
            }

            private void applyBorders(HSSFCellStyle style) {
                style.setBorderBottom(CellStyle.BORDER_THIN);
                style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                style.setBorderLeft(CellStyle.BORDER_THIN);
                style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                style.setBorderRight(CellStyle.BORDER_THIN);
                style.setRightBorderColor(IndexedColors.BLACK.getIndex());
                style.setBorderTop(CellStyle.BORDER_THIN);
                style.setTopBorderColor(IndexedColors.BLACK.getIndex());
            }

            private void mergeRow(HSSFRow row) {
                sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, 6));
            }

            private HSSFCellStyle getDefaultStyle(HSSFCell cell){
                HSSFCellStyle style = workbook.createCellStyle();
                style.setFont(normal);
                return style;
            }

            private void arrangeColumns() {
                HSSFCellStyle style = workbook.createCellStyle();
                style.setFont(normal);

                sheet.setColumnWidth(0, 256 * 4);
                sheet.setDefaultColumnStyle(0, style);

                sheet.setColumnWidth(1, 256 * 8);
                sheet.setDefaultColumnStyle(1, style);

                sheet.setColumnWidth(2, 256 * 50);
                sheet.setDefaultColumnStyle(2, style);

                sheet.setColumnWidth(3, 256 * 6);
                sheet.setDefaultColumnStyle(3, style);

                sheet.setColumnWidth(4, 256 * 5);
                sheet.setDefaultColumnStyle(4, style);

                sheet.setColumnWidth(5, 256 * 11);
                sheet.setDefaultColumnStyle(5, style);

                sheet.setColumnWidth(6, 256 * 12);
                sheet.setDefaultColumnStyle(6, style);
            }


            private File renderFile() throws ExportException {
                // Write the output to a file
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                try {
                    workbook.write(baos);
                } catch (IOException ex) {
                    throw new RuntimeException("Shold not happen", ex);
                }
                File workbookFile = new File("report/workbook-" + documentId + "-at-" + Pivot.now() + ".xls");
                try {
                    FileUtils.writeByteArrayToFile(workbookFile, baos.toByteArray());
                } catch (IOException ex) {
                    throw new ExportException("Не произвести экспорт документа: " + documentId, ex);
                }
                return workbookFile;
            }
        }.call();
    }
}
